/**
 * 
 */
package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.LinkedList;

import domain.Article;
import domain.Computer;
import domain.Laptop;
import domain.LocalPrinter;
import domain.NetworkPrinter;
import domain.Printer;
import domain.Product;
import domain.Server;
import domain.Supplier;
import domain.User;

/**
 * @author grupo22
 *
 */
public class JDBCProductDAO implements IProductDAO {
	
	public Collection selectAllProducts(){
		Collection result = new LinkedList();
		
		Connection con = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
        	String sql = "SELECT * FROM Product;";
        	stmt = con.prepareStatement(sql);
            
            rs = stmt.executeQuery();
            //System.out.println("Sentencia: " + stmt.toString());

            while (rs.next()) {
            	
            	String OIDProduct = new String(rs.getString("OIDProduct"));
            	String brand = new String(rs.getString("brand"));
            	String model = new String(rs.getString("model"));
            	double price = new Double(rs.getDouble("price"));
            	Boolean isValid = new Boolean(rs.getBoolean("isValid"));
            	Boolean validate = new Boolean(rs.getBoolean("validate"));
            	int numVotes = new Integer(rs.getInt("numVotes"));
            	Boolean contracted = new Boolean(rs.getBoolean("contracted"));
            	String OIDArticle = new String(rs.getString("OIDArticle"));
            	String OIDSupplier = new String(rs.getString("OIDSupplier"));
            	IArticleDAO artdao = new JDBCArticleDAO();
            	Article article = artdao.select(con, OIDArticle);
            	ISupplierDAO supdao = new JDBCSupplierDAO();
            	Supplier supplier = supdao.select(con, OIDSupplier);
            	
            	//Obtenemos el tipo de producto
            	String objectType = new String(rs.getString("objectType"));
            	
            	//Si es un ordenador
            	if (objectType.compareToIgnoreCase("computer") == 0){
            		Computer c = new Computer(brand, model, price, article, supplier);
            		IComputerDAO compdao = new JDBCComputerDAO();
            		c = compdao.select(con, OIDProduct, c);
            		c.setValid(isValid);
            		c.setValidate(validate);
            		c.setNumVotes(numVotes);
            		c.setContracted(contracted);
            		result.add(c);
            	} else if (objectType.compareToIgnoreCase("server") == 0){
            		Server s = new Server(brand, model, price, article, supplier);
            		IServerDAO servdao = new JDBCServerDAO();
            		s.setValid(isValid);
            		s.setValidate(validate);
            		s.setNumVotes(numVotes);
            		s.setContracted(contracted);
            		s = servdao.select(con, OIDProduct, s);
            		result.add(s);
            	} else if (objectType.compareToIgnoreCase("printer") == 0){
            		IPrinterDAO servdao = new JDBCPrinterDAO();
            		Printer p = servdao.select(con, OIDProduct, brand, model, price, article, supplier);
            		p.setValid(isValid);
            		p.setValidate(validate);
            		p.setNumVotes(numVotes);
            		p.setContracted(contracted);
            		result.add(p);
            	}
            }
        } catch (SQLException e) {
            System.out.println("Message: Fallo en JDBCProductDAO.selectAllProducts" + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(con);
        	try {
                    if (rs != null)
                            rs.close();
                    if (stmt != null)
                            stmt.close();
            } catch (SQLException e) {}
        }

		return result;
	}

	@Override
	public void updateProduct(Product p) {
		Connection con = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        try {
        	String sql = "UPDATE Product SET brand=?, model=?, price=?, isValid=?, validate=?, numVotes=?, contracted=? WHERE brand = ? and model = ?";
        	stmt = con.prepareStatement(sql);
        	stmt.setString(1, p.getBrand());
        	stmt.setString(2, p.getModel());
        	stmt.setDouble(3, p.getPrice());
        	stmt.setBoolean(4, p.isValid());
        	stmt.setBoolean(5, p.isValidate());
        	stmt.setInt(6, p.getNumVotes());
        	stmt.setBoolean(7, p.isContracted());
        	stmt.setString(8, p.getBrand());
        	stmt.setString(9, p.getModel());
        	//System.out.println("Sentencia: " + stmt.toString());
        	
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Message: Fallo en JDBCProductDAO.updateProduct " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(con);
        	try {
        		if (stmt != null)
        			stmt.close();
            } catch (SQLException e) {}
        }
		
	}
	
	@Override
	public Product select(Connection con, String oid) {
		String sql = "SELECT * FROM Product WHERE (OIDProduct = ?)";
		PreparedStatement stmt = null;
        ResultSet rs = null;
        Product p = null;

        try {
            stmt = con.prepareStatement(sql);
            stmt.setString(1, oid);

            rs = stmt.executeQuery();

            rs.next();
            String OIDProduct = new String(rs.getString("OIDProduct"));
        	String brand = new String(rs.getString("brand"));
        	String model = new String(rs.getString("model"));
        	double price = new Double(rs.getDouble("price"));
        	Boolean isValid = new Boolean(rs.getBoolean("isValid"));
        	Boolean validate = new Boolean(rs.getBoolean("validate"));
        	int numVotes = new Integer(rs.getInt("numVotes"));
        	Boolean contracted = new Boolean(rs.getBoolean("contracted"));
        	
        	String OIDArticle = new String(rs.getString("OIDArticle"));
        	String OIDSupplier = new String(rs.getString("OIDSupplier"));
        	IArticleDAO artdao = new JDBCArticleDAO();
        	Article article = artdao.select(con, OIDArticle);
        	ISupplierDAO supdao = new JDBCSupplierDAO();
        	Supplier supplier = supdao.select(con, OIDSupplier);
        	
        	//Obtenemos el tipo de producto
        	String objectType = new String(rs.getString("objectType"));
        	//Si es un ordenador
        	if (objectType.compareToIgnoreCase("computer") == 0){
        		Computer c = new Computer(brand, model, price, article, supplier);
        		IComputerDAO compdao = new JDBCComputerDAO();
        		c = compdao.select(con, OIDProduct, c);
        		c.setValid(isValid);
        		c.setValidate(validate);
        		c.setNumVotes(numVotes);
        		c.setContracted(contracted);
        		p = (Computer) c;
        	} else if (objectType.compareToIgnoreCase("server") == 0){
        		Server s = new Server(brand, model, price, article, supplier);
        		IServerDAO servdao = new JDBCServerDAO();
        		s.setValid(isValid);
        		s.setValidate(validate);
        		s.setNumVotes(numVotes);
        		s.setContracted(contracted);
        		s = servdao.select(con, OIDProduct, s);
        		p = (Server) s;
        	} else if (objectType.compareToIgnoreCase("printer") == 0){
        		IPrinterDAO servdao = new JDBCPrinterDAO();
        		Printer pr = servdao.select(con, OIDProduct, brand, model, price, article, supplier);
        		//Atributos propios de producto
        		pr.setValid(isValid);
        		pr.setValidate(validate);
        		pr.setNumVotes(numVotes);
        		pr.setContracted(contracted);
        		p = (Printer) pr;
        	}
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) { }
        }
        return p;
	}
	
	public String selectOID(Connection con, String brand, String model) {
		String sql = "SELECT OIDProduct FROM Product WHERE (brand = ? AND model = ? )";
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String s = null;

        try {
            stmt = con.prepareStatement(sql);
            stmt.setString(1, brand);
            stmt.setString(2, model);

            rs = stmt.executeQuery();

            rs.next();
            s = rs.getString("OIDProduct");

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) { }
        }
        return s;
	}
	
	@Override
	public void insert(Product p) {
		Connection con = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String oidp = java.util.UUID.randomUUID().toString();
		IArticleDAO idao = new JDBCArticleDAO();
		String oida = idao.selectOID(con, p.getArticle().getCode());
		ISupplierDAO sdao = new JDBCSupplierDAO();
		String oids = sdao.selectOID(con, p.getSupplier().getBidderCode());
		String sql = "INSERT INTO Product (OIDProduct, OIDArticle, OIDSupplier, brand, model, price, numVotes, contracted, objectType) VALUES (?,?,?,?,?,?,?,?,?)";
        try {
        	stmt = con.prepareStatement(sql);
        	stmt.setString(1, oidp);
        	stmt.setString(2, oida);
        	stmt.setString(3, oids);
        	stmt.setString(4, p.getBrand());
        	stmt.setString(5, p.getModel());
        	stmt.setDouble(6, p.getPrice());
        	stmt.setInt(7, 0);
        	stmt.setInt(8, 0);
        	if (p.getArticle().getType().compareTo("Ordenador")==0){
        		stmt.setString(9, "computer");   	
        		System.out.println("Sentencia: " + stmt.toString());
        		
        		stmt.executeUpdate();
    			
        		IComputerDAO cdao = new JDBCComputerDAO();
    			cdao.insert(con, (Computer) p, oidp);
    		} else if (p.getArticle().getType().compareTo("Portatil")==0){
        		stmt.setString(9, "computer");   	
        		System.out.println("Sentencia: " + stmt.toString());
        		
        		stmt.executeUpdate();
    			
        		IComputerDAO cdao = new JDBCComputerDAO();
    			cdao.insert(con, (Laptop) p, oidp);
    		} else if (p.getArticle().getType().compareTo("Servidor")==0){
        		stmt.setString(9, "server");   	
        		System.out.println("Sentencia: " + stmt.toString());
        		
        		stmt.executeUpdate();
    			
        		IServerDAO cdao = new JDBCServerDAO();
    			cdao.insert(con, (Server) p, oidp);
    		} else if (p.getArticle().getType().compareTo("Impresora Local")==0){
        		stmt.setString(9, "printer");   	
        		System.out.println("Sentencia: " + stmt.toString());
        		
        		stmt.executeUpdate();
    			
        		IPrinterDAO cdao = new JDBCPrinterDAO();
    			cdao.insert(con, (LocalPrinter) p, oidp);
    		} else if (p.getArticle().getType().compareTo("Impresora Red")==0){
        		stmt.setString(9, "printer");   	
        		System.out.println("Sentencia: " + stmt.toString());
        		
        		stmt.executeUpdate();
    			
        		IPrinterDAO cdao = new JDBCPrinterDAO();
    			cdao.insert(con, (NetworkPrinter) p, oidp);
    		}
        } catch (SQLException e) {
            System.out.println("Message: Fallo en JDBCProductDAO.insert " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(con);
        	try {
        		if (stmt != null)
        			stmt.close();
            } catch (SQLException e) {}
        }

	}
}
